home
***
CD-ROM
|
disk
|
FTP
|
other
***
search
/
Fritz: All Fritz
/
All Fritz.zip
/
All Fritz
/
FILES
/
SPREOTUS
/
123PWRWK.LZH
/
ROR.DOC
< prev
next >
Wrap
Text File
|
1984-09-09
|
27KB
|
661 lines
ROR - Rate of Return Calculator
1. INTRODUCTION
This worksheet calculates the rate of return of a cash flow.
Rate of return is notionally equivalent to the interest or
discount rate that reduces the net present value of the cash flow
to zero. (The net present value of a cash flow at different
discount rates is calculated by the NPV worksheet.)
Rate of return is one measure of the value of a cash flow
that takes into account the time value of money (NPV is another
measure). The higher the rate of return, the more you can
discount future earnings and costs before the project becomes
unprofitable.
There are many ways to calculate rate of return, depending on
your assumptions about financing sources and profit reinvestment
opportunities. This worksheet uses three methods, described
below. The PAYOUT worksheet (on the Volume 1B disk) calculates a
project payout schedule, given the cash flow and interest rates
for loans and investments. That worksheet takes into account the
positive and negative balances accruing to the project. It also
has a goal-seeking option to find the loan interest rate that
reduces the project closing balance to zero. This is another way
of calculating a rate of return for the project.
2. CALCULATION METHODS
2.1 Introduction
As stated above, this worksheet
uses three methods. Each method corresponds to a different set
of assumptions about financing sources and profit reinvestment
opportunities. The three
methods typically lead to different values for the rate of return.
This does not mean that one is right and the others are wrong;
each one is correct according to its set of assumptions. The
following descriptions include a discussion of the advantages and
disadvantages of each method of calculation.
2.2 Internal Rate of Return
The first (and simplest) method of calculating rate of return
assumes that loans and investments make the same rate of return as
the project being evaluated. For example, if the rate of return
calculated by this method is 30%, the assumption is that the
interest rate on money borrowed to finance this project and the
yield obtained by reinvesting the profits are both 30% too.
Nevertheless, this method of calculating rate of return is the
most widely used. The result of this method is known as the
internal rate of return (IRR) because it makes no reference to
factors (such as discount or reinvestment rates) outside the cash
flow.
09/09/84 ROR - 1
The IRR method may fall down when the cash flow is not 'well
behaved'. The typical well-behaved cash flow has cash outflows at
the beginning, followed by cash inflows. When there is more than
one change of sign in the cash flow values, this method may yield
multiple rates of return. Such cash flows can occur (for example)
in income acceleration projects in the resource industries. Here,
an initial investment leads to an increased cash flow in early
years, but a reduced cash flow (over the 'do nothing' alternative)
in later years as the accelerated resource depletion takes its
toll. The cash flow for this project (compared with 'do nothing')
starts out negative, becomes positive, then returns to negative
again. Application of the simple method can yield two rates of
return - one reasonable, and one ridiculously large. The large
rate is mathematically correct, but assumes one could obtain that
same rate of return by reinvesting the profits from the project,
which is not usually practical.
2.3 Modified Internal Rate of Return
The second method avoids the multiple rate of return problem
by discounting all cash outflows to a single value in the opening
period using a specified discount rate. This single value may be
though of as the amount that would have to be invested now at the
discount rate to support the outflows. The cash outflows are then
replaced by this single outflow in period zero to give a modified
cash flow (which is then 'well behaved'). The rate of return of
this cash flow is calculated using the method described above.
The assumption behind this method is that an amount is invested
now at the discount rate and the proceeds are used to finance the
cash outflows. The rate of return is the rate which this amount
would have to yield (if invested) in order to return the same
positive cash flows (in amount and timing) as the cash flow
stream.
2.4 Financial Management Rate of Return
The third method goes one step further. It begins, like the
second method, by discounting all cash outflows to a single value
in the opening period using the specified discount rate. Then it
calculates the amount that would accrue in the final period if all
cash inflows were reinvested at a specified reinvestment rate for
the remainder of the project duration. The rate of return by this
method is the rate which the single value in the opening period
would have to yield (if invested) in order to accrue to the same
amount as the reinvested cash inflow values yield in the final
period.
2.4 General Comments
There are many sets of assumptions which one could defend
when evaluating a project. Each of the methods described above
has its flaws. The first assumes that surpluses yield the same
rate of return if reinvested (though supluses do not occur if the
rate of return is applied to a well-behaved cash flow). The
second and third methods do not take proper account of the
09/09/84 ROR - 2
deficits and surpluses that occur during the life of the project.
The PAYOUT worksheet calculates a payout schedule for a project,
given its cash flow and borrowing and reinvestment rates. This
schedule calculates the closing balance for each period, and uses
the appropriate rate depending on the sign of the balance. In
this respect, it may be thought of as 'more accurate'. It also
has a goal-seeking option, which yields the borrowing rate that
reduces to zero the closing balance in the final period (ie.
produces a breakeven project). Experimenting with the PAYOUT
worksheet can give a better understanding of rate of return. The
PAYOUT worksheet is contained on the Volume 1B disk in this
series.
3. USING ROR
3.1 Overview
The main menu of the ROR worksheet offers seven options (plus
Quit). They are as follows:
Input
This option leads to a menu of the items you may input.
This sub-menu allows you to enter the cash flow values by
period, the number of periods corresponding to a year, the
discount rate for cash outflows, the reinvestment rate for
cash inflows, and a first estimate of the internal rate of
return. The input process is described in more detail
below.
Blank
This option blanks out all input data and results (after
receiving confirmation that this is what you intend). It is
useful for clearing the worksheet before entering an
entirely new set of data.
Calc
The calculate option performs all calculations and displays
the three rates of return.
Results
This shows the results of the calculations. If there are
more than eleven periods in the cash flow (ie. it extends
beyond the bottom of the home screen) this option yields a
scroll menu that allows you to scroll the cash flow column
up and down to view the entire cash flow.
Graph
Two graphs are available in this worksheet. One shows the
cash flow and cumulative cash flow. The other shows the net
present value of the cash flow at discount rates between 0
and 100 percent. Both graphs can be displayed in monochrome
or color, as line charts, bar charts, or scatter (points
only) graphs.
09/09/84 ROR - 3
Print
The print option prints the cash flow, cumulative cash flow,
and results area.
Agenda
If you select this option (and confirm it when requested),
the current worksheet is lost and replaced by the worksheet
selection agenda. From there, you can load another
worksheet. Note confirmation is required before this option
proceeds because it overwrites the worksheet in memory.
3.2 Input Details
The Input option in the main menu leads to a sub-menu with
five options - Cashflow, Periods/Yr, DiscRate, ReinvRate, and
EstIRR. Each option allows you to enter part of the data.
The Cashflow option allows you to enter cash flow values, by
period. This option places the cell pointer at the top of the
cash flow column, and moves it down one cell each time you press
the Enter key. If you want to enter the entire cash flow, simply
type each number in turn and press the Enter key after each one.
Please note the following:
- A value must be entered for each period covering the
entire duration of the project and starting at period
zero. If the cash flow value for a particular period is
zero, enter zero. Do not leave the cell blank.
- Period zero is now, period one is one period (year,
month, or whatever) hence.
- Cash outflows must be entered as negative numbers; cash
inflows must be entered as positive numbers.
If you don't want to enter the whole cash flow (for example,
if you want to adjust a cash flow you've already entered) you may
use the cursor movement keys to place the cell pointer on any
number you want to change, and re-type it in normal 1-2-3 fashion.
When you press the Enter key, the macro will move the cell pointer
down one row.
The macro which drives this process has no way of knowing
when you've finished entering or changing data. When you have
finished, you must break out of the macro and re-invoke the input
menu. To do this, hold down the Ctrl key and press the Break key.
The CTL flag at the top of the screen will go out. Then hold down
the Alt key and press I to invoke the input menu.
The Periods/Yr option accepts the number of periods that
correspond to a year. If you have annual data, you must enter 1.
If you have monthly data, enter 12. This parameter is used to
convert the discount and reinvestment rates you enter from annual
rates to rates per period. It is also used to convert the rates
of return from rates per period to annual rates. Whatever the
periods your data represents, you always enter rates, and read
09/09/84 ROR - 4
results as annual rates.
The third choice in the input menu is DiscRate. It allows
you to enter the annual rate used to discount cash outflows back
to period zero.
The fourth choice is ReinvRate. It allows you to enter the
annual re-investment rate used to accumulate the future value of
the cash inflows in the final period.
The final option is EstIRR. Normally, you should leave this
at zero, and the worksheet will choose its own first estimate of
the internal rate of return. The worksheet will choose a value
which is close to the first change of sign of the net present
value. If the graph of net present value crosses the X-axis more
than once, there is more than one internal rate of return. In
this case, you may enter a discount rate close to the second
crossing, and the worksheet will calculate its rate of return
The last three options expect to receive only one number. To
enter the number, simply select the option from the input menu,
type the number, and press the Enter key.
3.3 Graph Details
This worksheet can produce two different graphs on either the
color or monochrome display. Each graph can take the form of a
line graph, a bar chart, or a scatter graph (points without
connecting lines). These are selected by means of a menu with
four choices.
The Cashflow graph displays the cash flow and cumulative cash
flow by period. The form of the graph will be the same as the one
that was last displayed. The NPV graph shows the net present
value against the discount rate in steps of 5% from 0% to 100%.
It shows how the net present value behaves as future values are
discounted more or less heavily. The discount rate (if any) at
which the graph crosses the X-axis represents the rate which
reduces the net present value of the cash flow to zero. This is
known as the internal rate of return of the cash flow.
The Type option presents a sub-menu from which you can select
the form of the graph. The three choices are Line, Bar, and
Scatter. Each choice re-displays the last graph in the selected
form, then returns to the graph menu.
The fourth choice - Options - allows you to choose the Color
or monochrome (B&W) monitor for the display. Again, each choice
re-displays the last graph on the selected device.
4. EXAMPLES
4.1 A Simple Investment
This example illustrates the use of this worksheet for a
09/09/84 ROR - 5
well-behaved cash flow. You have been given the opportunity to
purchase a note for $15,000. The note will return $5,000 at the
end of the first year, $7,000 at the end of the second year, and
$9,000 at the end of the third year. What is the internal rate of
return of this investment? Assuming you plan to re-invest the
returns from the note at 13% until the end of the third year, what
is the rate of return of this plan over the three year period.
(Note, method two does not apply in this case, because there is
only one cash outflow in period zero.)
Keystroke Comments
{ENTER} Select the Input option from the main menu.
P 1 {ENTER} Number of periods per year (the data is annual)
C Select the Cashflow option. The cell pointer will
go to the top of the cash flow column.
-15000 {ENTER} Value for period zero (now). Since this is a
payment (ie. a cash outflow) it is entered as a
negative number. When you press the ENTER key,
the cell pointer will move down to the next cell
in the column.
5000 {ENTER} Value returned at the end of the first year. This
is a cash inflow.
7000 {ENTER} Value returned at the end of the second year.
9000 {ENTER} Value returned at the end of the third year.
{Ctrl}{Break} To terminate the input loop, hold down the Ctrl
key and press the Break key.
{Alt} I To re-invoke the input menu, hold down the Alt key
and press I.
R 13 {ENTER} Re-investment rate, as an annual percentage.
Q Return to the main menu.
C Select the Calc option. The rate of return will
be calculated using the three methods.
G Select the Graph option from the main menu.
text {ENTER} Whenever you enter the graph menu, the system will
prompt you to enter a second title line for the
graphs. You may leave this line blank, or enter
any short description. To get rid of an existing
description before typing a new one, press the Esc
key.
09/09/84 ROR - 6
N This option displays a graph of the net present
value against the discount rates. Press any key
to return to the graph menu.
T L If the graph is not already a line graph, you can
go to the Type sub-menu to specify a line graph.
The graph will be re-displayed. Press any key to
return to the graph menu.
4.2 An Accelerated Income Project
Many projects in the resources industry are aimed at
accelerating resource recoveries. This leads to increased cash
flows in early years, followed by compensating decreased cash
flows in later years. Thus the cash flow consists of a series of
outflows (the investment), followed by a series of inflows (the
increased cash flows) followed by another series of outflows (the
reduced cash flows). This is not a 'well-behaved' cash flow.
You are considering the value of an enhanced recovery
project. The immediate cost of this project is $600. It will
increase cash flows in the next three years by $800, $500, and
$200 respectively. In the three years after that, however, cash
flows will be reduced by $200, $500, and $800 respectively. You
need to know the rate of return of this project. Future cash
outflows can be discounted at 10% p.a., and you can re-invest cash
inflows at 18% p.a.
Keystroke Comments
{ENTER} Select the input option from the main menu.
P 1 {ENTER} Number of periods per year (the data is
annual).
C Select the Cashflow option from the input menu.
The cell pointer will go to the top cell of the
cash flow column.
-600 {ENTER} Initial investment (in period zero). The cell
pointer will move down to the next period when you
press the Enter key.
800 {ENTER} Increases in cash flows in the first three years.
500 {ENTER}
200 {ENTER}
-200 {ENTER} Decreases in cash flows in the next three years.
-500 {ENTER}
-800 {ENTER}
{Ctrl}{Break} Hold down the Ctrl key and press the Break key to
break out of the macro. The CTL flag at the top
of the screen will go out.
09/09/84 ROR - 7
Alt I To re-invoke the input menu, hold down the Alt key
and press I.
D 10 {ENTER} Select the DiscRate option in the input menu to
enter the rate used to discount negative cash
flows back to period zero.
R 18 {ENTER} Select the ReinvRate option in the input menu, and
enter the re-investment rate for positive cash
flows.
Q Return to the main menu.
C Invoke the calculations.
The results of the calculation show that the internal
rate of return is 24%. But if you can only discount the
future cash outflows at 10% p.a. (instead of the 24% that
IRR assumes), the rate of return falls to almost zero. And
even if you can guarantee an 18% return on the cash inflows,
the rate of return is still only 13.05%. At this point, you
might be intrigued about this cash flow. One way to look at
it is to examine its net present value under different
discount rates. To do this, enter the following:
G Select the graph option.
text {ENTER} Each time you invoke the graph menu, it asks you
to enter the second title line for the graphs.
Enter any short description. To remove an
existing description before typing a new one,
press the Esc key.
N Select the NPV option in the graph menu to display
a graph of the net present value against the
annual discount rates. Press any key to return to
the graph menu.
T L Select a line graph. The graph will immediately
be re-displayed.
This graph shows that the net present value peaks at
under $50 when the discount rate is around 40%. But this
assumes that you can get a 40% rate of return on
investments, which is unlikely. And even then, the net
present value is very small. Clearly this is not a
profitable project, even though the internal rate of return
is impressive. You will also see that the net present value
curve crosses the X-axis at two points. The worksheet will
converge on the leftmost point, unless you enter a first
estimate of the rate of return that is closer to the
rightmost one. To find the rightmost rate of return, enter
(say) 65 for EstIRR in the input menu, and repeat the
calculations.
To exit from the menus, type the following:
09/09/84 ROR - 8
Q Return to the main menu.
Q Exit to 1-2-3 READY mode. If you want to return
to the main menu, hold down the Alt key and type
M.
09/09/84 ROR - 9
Menu Tree for ROR Worksheet.
Input - Enter Cashflow Values and Discount/Reinvestment Rates
. Periods/Yr - Enter number of periods per year
. Cashflow - Enter cashflow values
. DiscRate - Enter annual discount rate for cash outflows
. ReinvRate - Enter annual reinvestment rate for cash inflows
. Quit - Return to main menu
Blank - Clear Input Fields
. No - Cancel "Blank" Command
. Yes - Yes, Erase all input
Calc - Calculate Results
Results - Show Results
. Down - Scroll down one screen
. Up - Scroll up one screen
. Quit - Return to main menu
Graph - Display Graphs
. Cashflow - Display cashflow and cumulative cashflow
. NPV - Display net present value vs. discount rate
. Type - Select line graph, bar chart, or scatter plot
. . Line - Display line graph
. . Bar - Display bar chart
. . Scatter - Display scatter graph
. Options - Choose color or monochrome monitor
. . Color - Display graphs in color
. . B&W - Display graphs in monochrome
. Quit - Return to Menu
Print - Print Results
Agenda - Return to Worksheet Selection Agenda
. No - Do not clear this worksheet. Return to main menu
. Yes - Clear this worksheet. Go to selection agenda
Quit - Exit to 1-2-3 READY mode
09/09/84 ROR - 10
